package DBLag;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLag.*;

public class DbMedarbejder {
	private Connection con;

	public DbMedarbejder() {
		con = DbConnection.getInstance().getDBcon();
	}

	public int tilfoejMedarbejder(Person p) {
		int rc = -1;
		PreparedStatement pstmt = null;
		String insert = "insert into Medarbejder(pId, stilling)" + "?,?";
		System.out.println(insert);
		try {
			pstmt = con.prepareStatement(insert);
			tilfoejPerson(p);
			pstmt.setInt(1, p.getpId());
			pstmt.setString(2, p.getStilling());

			rc = pstmt.executeUpdate();
		} catch (SQLException sqlE) {
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		return rc;
	}

	private int tilfoejPerson(Person p) {
		int rc = -1;
		PreparedStatement pstmt = null;

		String insert = "insert into Person(navn, adresse, telefonnummer, postNr, pId, personType)"
				+ "values (?,?,?,?,?,?)";
		System.out.println(insert);
		try {
			pstmt = con.prepareStatement(insert);
			pstmt.setString(1, p.getNavn());
			pstmt.setString(2, p.getAddresse());
			pstmt.setString(3, p.getTlfNr());
			pstmt.setInt(4, p.getPostNr());
			pstmt.setInt(5, p.getpId());
			pstmt.setString(6, "Medarbejder");

			rc = pstmt.executeUpdate();
		}

		catch (SQLException sqlE) {
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		return rc;
	}

	public int opdaterMedarbejder(Person p) {
		Person pObj = p;
		int rc = -1;
		opdaterPerson(p);
		String query = "UPDATE Medarbejder SET " + "Stilling ='" + pObj.getStilling()
				+ "'" + " WHERE PId = '"
				+ pObj.getpId() + "'";
		System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
		}
		catch(Exception ex){
			System.out.println("Medarbejderen kunne ikke opdateres: " + ex);
		}
		
		return rc;
	}
	
	private int opdaterPerson(Person p){
		Person pObj = p;
		int rc = -1;
		
		String query = "UPDATE person SET " + "navn ='" + pObj.getNavn()
				+ "', " + "adresse ='"
				+ pObj.getAddresse() + "', " + "telefonnummer ='"
				+ pObj.getTlfNr() + "' "
				+ ", " + "postNr ='" + pObj.getPostNr() +"'"
				+ " WHERE PId = '"
				+ pObj.getpId() + "'";
		System.out.println("Update query:" + query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
		}
		catch(Exception ex){
			System.out.println("Personen kunne ikke opdateres: " + ex);
		}
		
		return rc;
	}
	
	public int sletMedarbejder(int pId) {
		int rc = -1;
		PreparedStatement pstmt = null;
		String slet = "delete from Medarbejder, Person where pId = ?";
		System.out.println("Sletter fra Medarbejder" + slet);
		try{
			pstmt = con.prepareStatement(slet);
			pstmt.setInt(1, pId);
			rc = pstmt.executeUpdate();
			System.out.println("Medarbejderen er blevet slettet fra databasen");
		}
		catch(SQLException sqlE){
			System.out.println(sqlE.getMessage());
		}
		catch(Exception e){
			System.out.println(e.getMessage());
		}
		
		return rc;
	}
	
	public Person findMedarbejder(int pId) {
		String wClause = " Medarbejder.pId = '" + pId + "'"+"and" + " Person.pId = '" + pId + "'";
		return singleWhere(wClause);
		
	}
	
	public ArrayList<Person> findMedarbejderPaaNavn(String Navn)
	{
		String wClause = " Person.Navn like  '%" +  Navn  + "%'" +"and Person.persontype = 'medarbejder'";
		return listAlleMedarbejdere(wClause);
	}
	
	private Person singleWhere(String wClause){
		ResultSet results;
		Person p = new Medarbejder();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next()){
				p = buildMedarbejder(results);
				stmt.close();
			} else {
				p = null;
			}
			
		}
		catch(Exception exe){
			
		}
		return p;
	}
	
	private String buildQuery(String wClause) {
		String query = "SELECT * FROM Medarbejder, Person";
		if (wClause.length() > 0) {
			query = query + " WHERE " + wClause;
		}
		return query;
	}
	
	private Person buildMedarbejder(ResultSet results) {
		Person p = new Patient();

		try {
			p.setpId(results.getInt("PId"));
			p.setAddresse(results.getString("adresse"));
			p.setNavn(results.getString("navn"));
			p.setPostNr(results.getInt("postNr"));
			p.setTlfNr(results.getString("telefonnummer"));
			p.setStilling(results.getString("Stilling"));
		}
		catch(Exception e){
		System.out.println("Der skete en fejl:  " + e);	
		}
		return p;
	}
	
	public ArrayList<Person> listAlleMedarbejdere(String wClause) 
	{
		ResultSet results;
		ArrayList<Person> list = new ArrayList<Person>();

		String query = buildQuery(wClause);

		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) 
			{
				Person pObj = new Medarbejder();
				pObj = buildMedarbejder(results);
				list.add(pObj);
			}// end while
			stmt.close();
			

		}// slut try
		catch (Exception e) 
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

}
